package es.ats.codington.festival.daos;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;



import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;



import es.ats.codington.festival.domain.Imagen;
import es.ats.codington.festival.domain.Place;

import es.ats.codington.festival.exceptions.DAOException;
import es.ats.codington.festival.interfaces.daos.IPlaceDAO;

import es.ats.codington.festival.mappers.ImageMapper;
import es.ats.codington.festival.mappers.PlaceMapper;

/**
 * @author Codington_RED
 *
 */

public class PlaceDAOImpl implements IPlaceDAO{

	private JdbcTemplate jdbcTemplateObject;
	private Properties queryProperties;
	
	Connection conn;
	int key;
	
	/**
	 * Default constructor for PlaceDAOImpl
	 * @param conn
	 */
	
	public PlaceDAOImpl(JdbcTemplate jdbcTemplateObject,Properties queryProperties){
		this.jdbcTemplateObject = jdbcTemplateObject;
		this.queryProperties = queryProperties;
	}
	
	
	/**
	 * obtain a list with every place in the database table
	 * @param resultSet
	 * @return places
	 * @throws SQLException
	 */
	private List<Place> getPlaceList(ResultSet resultSet) throws SQLException {
		
		List<Place> places = new ArrayList<Place>();
		
		while(resultSet.next()) {
			
			Place place = new Place();
			
		    place.setId(resultSet.getInt("idPlace"));
		    place.setNamePlace(resultSet.getString("namePlace"));
		    place.setCapacityPlace(resultSet.getInt("capacityPlace"));
		    place.setPlaceDescription(resultSet.getString("placeDescription"));
		    place.setZonePlace(resultSet.getString("zonePlace"));
		    place.setImagePlace(resultSet.getString("imagePlace"));
		 
		    places.add(place);
		    
		}
		
		return places;
	}
	
	/**
	 * get the place from the database table
	 * @param idPlace
	 * @return places.get
	 * @throws DAOException 
	 */
	@Override
	public Place getPlace(final int idPlace) throws DAOException {
		
		List<Place> places = jdbcTemplateObject.query(queryProperties.getProperty("getPlace"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						 ps.setInt(1, idPlace);
						
					}
			
		}, new PlaceMapper());
		
		return places.get(0);
		
	}
	
	/**
	 * add a new place to the database table
	 * @param typePlace
	 * @param place
	 * @return insertPlace
	 * @throws DAOException 
	 */
	@Override
	public boolean insertPlace(final Place place, String typePlace) throws DAOException {
		
		boolean insertPlace = false;
		Integer affectedRows = 0;
		int typePlaceNum = 0;
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		affectedRows = jdbcTemplateObject.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps =
                        conn.prepareStatement(queryProperties.getProperty("insertPlace"), PreparedStatement.RETURN_GENERATED_KEYS);
						
						ps.setString(1, place.getNamePlace());
						ps.setInt(2, place.getCapacityPlace());
						ps.setString(3, place.getPlaceDescription());
						ps.setString(4, place.getZonePlace());
						ps.setString(5, place.getImagePlace());
						
						return ps;
			}
			
		}, keyHolder);
		
		key = keyHolder.getKey().intValue();
		place.setId(key);
			
			
		if(typePlace.equals("LBussines")){
			
			typePlaceNum = 1;
			
		} else if(typePlace.equals("Museum")){
			
			typePlaceNum = 2;
			
		} else if(typePlace.equals("Park")){
			
			typePlaceNum = 3;
			
		} else if(typePlace.equals("Stadium")){
			
			typePlaceNum = 4;
			
		} else if(typePlace.equals("Theater")){
			
			typePlaceNum = 5;
			
		} else if(typePlace.equals("TAttaction")){
			
			typePlaceNum = 6;
			
		} else if(typePlace.equals("TMarket")){
			
			typePlaceNum = 7;
			
		} else if(typePlace.equals("Zoo")){
			
			typePlaceNum = 8;
			
		} else {
			
			typePlaceNum = 1;
		}
		
		switch(typePlaceNum){
			//LBusiness-Building
			case 1:
				jdbcTemplateObject.update("INSERT INTO building (idBuilding) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				jdbcTemplateObject.update("INSERT INTO lbussines (idLBussines) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				break;
				
				
			//Museum-Building
			case 2:
				jdbcTemplateObject.update("INSERT INTO building (idBuilding) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				jdbcTemplateObject.update("INSERT INTO museum (idMuseum) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				
				break;
				
			//Park
			case 3:	
				jdbcTemplateObject.update("INSERT INTO park (idPark) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				break;
				
			//Stadium-Building
			case 4:
				jdbcTemplateObject.update("INSERT INTO building (idBuilding) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				jdbcTemplateObject.update("INSERT INTO stadium (idStadium) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				break;
				
			//Theater-Building
			case 5:
				jdbcTemplateObject.update("INSERT INTO building (idBuilding) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				jdbcTemplateObject.update("INSERT INTO theater (idTheater) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				break;
				
			//TAttraction
			case 6:
				jdbcTemplateObject.update("INSERT INTO tattraction (idTAttraction) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				break;
				
			//TMarket
			case 7:
				jdbcTemplateObject.update("INSERT INTO tmarket (idTMarket) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
				break;
				
			//Zoo
			case 8:
				jdbcTemplateObject.update("INSERT INTO zoo (idZoo) values (?)", 
						new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						
					}
				});
				
		
				break;				
		}
		    
		if(affectedRows > 0){
			insertPlace = true;
		}
			
		return insertPlace;
	}
	
	
	/**
	 * Allows us update all the information relative to a place
	 * @param place
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updatePlace(final Place place) throws DAOException {
		
		Integer affectedRows = 0;
		
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("updatePlace"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
							ps.setString(1, place.getNamePlace());
						    ps.setInt(2, place.getCapacityPlace());
						    ps.setString(3, place.getPlaceDescription());
						    ps.setString(4, place.getZonePlace());
						    ps.setString(5, place.getImagePlace());
						    ps.setInt(6, place.getId());
						
					}
		});
		
		return affectedRows;
		
	}
	
	/**
	 *show a list of all places
	 *@return places 
	 *@throws DAOException 
	 */
	@Override
	public List<Place> showAllPlaces() throws DAOException {
		
		List<Place> places = jdbcTemplateObject.query(queryProperties.getProperty("allPlaces"), 
				new PlaceMapper());
		
		return places;
		
	}
	
    /**
     * show the image
     *@return list.get 
	 *@throws FileNotFoundException 
     */
	@Override
    public Imagen imageSpring(String route, final String name) throws FileNotFoundException {
    	
    	
    	final File file = new File(route);
    	Imagen img = new Imagen();
  
    	
    	KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplateObject.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps =
                        conn.prepareStatement("INSERT INTO images(image,name) values(?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
						
							FileInputStream fis = null;
							try {
								
								fis = new FileInputStream(file);
								
							} catch (FileNotFoundException e) {
							
								e.getMessage();
							}
						  	ps.setBinaryStream(1,fis,(int)file.length());
				            ps.setString(2, name);
							
				            return ps;
			}
			
		}, keyHolder);
		
		key = keyHolder.getKey().intValue();
		img.setImageID(key);
    	 
    	
    	List<Imagen> list = jdbcTemplateObject.query("SELECT * FROM images WHERE idImage = ?", 
				new PreparedStatementSetter(){

			@Override
			public void setValues(PreparedStatement ps)
					throws SQLException {
				
				 ps.setInt(1, key);
				
			}
	
    		}, new ImageMapper());
    	
    	return list.get(0);
    	
    }

}
